BudgetHack Data Processing - The Bureaucrats
mj
23 March 2016
Import Data
Here we import the Victoria spatial data for suburbs and the expenditure dataset. Once completed this will give a value per suburb in a format that can be mapped. The expenditure dataset is a bit horrible to get location names out of and there are a mix of suburb names, LGA names, regions and a mix of other spatial scales. Cleaning this is endless and I have to draw the line and move on.
Suburb Data
# spatial data
abs.suburbs <- rgdal::readOGR('mapping', layer='SSC_simple')## OGR data source with driver: ESRI Shapefile
## Source: "mapping", layer: "SSC_simple"
## with 8529 features
## It has 4 fields# clean list of ABS suburb names and hierarchy
ssc.csv <- read_csv('mapping/SSC_2011_AUST.csv') %>%
mutate(state = str_sub(SA1_MAINCODE_2011, 1,1)) %>%
filter(state == '2')
# SA1 gives us the hierarchy of nested regions
sa1.csv <- read_csv('mapping/SA1_2011_AUST.csv')
ssc <- left_join(ssc.csv, sa1.csv) %>%
select(SSC_CODE_2011, SSC_NAME_2011,
SA2_NAME_2011, SA3_NAME_2011, SA4_NAME_2011,
GCCSA_NAME_2011) %>%
filter(!(SSC_NAME_2011 %in% c("Migratory - Offshore - Shipping (Vic.)",
"No usual address (Vic.)",
"Unclassified (Vic.)"))) %>%
mutate(SSC_NAME_2011 = toupper(gsub(' \\(Vic.\\)', '', SSC_NAME_2011))) %>%
distinct(SSC_CODE_2011) %>%
rename(SSC_CODE = SSC_CODE_2011)
rm(ssc.csv, sa1.csv) # these are too large to leave in memory
suburb.names <- ssc$SSC_NAME_2011
# filter suburbs down to Cleaned Victoria
abs.sub <- abs.suburbs[abs.suburbs$SSC_CODE %in% ssc$SSC_CODE, ]
rm(abs.suburbs)Budget Data
# import budget data (projects/spending)
data <- read_excel('data/2015-16-State-Capital-Program.xlsx', skip=3, sheet='BP4') %>%
filter(`New / Existing / Completed` != 'Completed',
!grepl('All projects with a TEI less than $1 million', `Project Name`)) %>%
rowwise() %>%
mutate(vsplit = strsplit(`Project Name`, '\\(|\\)'),
vlen = length(vsplit)) %>%
filter(vlen > 1)
# vlen > 1 removes
# `Project Name` != "All remaining projects with a TEI less than $1 million",
# `Project Name` != "All projects with a TEI less than $1 million",
# `Project Name` != "Enterprise resource planning new business system implementation"
# Select columns and Write project data to html file to copy to final webpage
tbl <- select(data, `Project Name`, Department, `New / Existing / Completed`, `Total Estimated Investment`)
write(kable(tbl, 'html'), 'out_table.txt')
# separate location data out into single column, trying to catch errors and variation here
data$location <- sapply(data$vsplit, function(r) {
vals = grep('^[[:upper:]]|metro various|various|statewide', r)
val = r[vals[vals>1]]
if (identical(val, character(0))) {
return(NA)
} else if (length(val) > 1 & any(grepl('statewide', val))) {
return('statewide')
} else if (val[1] == "Commonwealth Games Village") {
return(val[2])
} else if (length(val) > 1 & any(grepl('non-metro various', val))) {
return('non-metro various')
} else if (grepl("Gippsland, Bendigo, Ballarat", val)) {
return('Gippsland/Bendigo/Ballarat/Geelong/Monash/Casey/Wyndham/Banyule/Yarra Ranges/Whittlesea')
} else {
return(val)
}
}
)Calculate Expenditure per Location
Some expenditure goes to multiple locations so this needs to be split out first. Then expenditure is summed for single locations.
df <- data %>%
mutate(location = iconv(location, to='ASCII'),
subs = strsplit(location, '/| and | - '),
nsubs = length(subs),
sub.value = as.numeric(`Total Estimated Investment`) / nsubs) %>%
unnest(subs) %>%
mutate(subs = toupper(str_trim(subs, side = 'both'))) %>%
group_by(subs) %>%
summarise(value = sum(sub.value, na.rm=TRUE)) %>%
mutate(subs = ifelse(is.na(subs), 'VARIOUS', subs),
value = value * 1000)Join Locations and Expediture to Suburbs
Some suburb names needed to be modified so that the Expenditure dataset and Suburbs spatial data would match. The plot below indicates the locations that have not been allocated to a suburb.
# fix suburb names
# FROM TO
rename.suburbs <- data.frame(rbind(
c('LOWER TEMPLESTOWE', 'TEMPLESTOWE LOWER'),
c('MT BULLER', 'MOUNT BULLER'),
c('MT MARTHA', 'MOUNT MARTHA'),
c('CURDIE VALE', 'CURDIEVALE'),
c('DINGLEY', 'DINGLEY VILLAGE'),
c('BALLARAT WEST', 'BALLARAT CENTRAL'),
c('BALLARAT', 'BALLARAT CENTRAL'),
c('EPPING NORTH', 'EPPING'),
c('KIALLA LAKES','KIALLA'),
c('EAST WERRIBEE', 'WERRIBEE'),
c('BROADFORD - KILMORE', 'BROADFORD'),
c('MT COTTRELL', 'MOUNT COTTRELL'),
c("HUNTLY NORTH", 'HUNTLY'),
c('WYNDHAM', 'WYNDHAM VALE'),
c('BARWON REGION', 'BARWON')
), stringsAsFactors = FALSE
)
df$subs <- mapvalues(df$subs, from = rename.suburbs[,1], to = rename.suburbs[,2])
write.csv(df, 'data/expenditure_by_location.csv', row.names = FALSE)
# filter expenditure data by locations in and out of the suburbs list plus statewide and other identifiable regions
df.suburbs <- filter(df, subs %in% suburb.names) %>%
group_by(subs) %>%
summarise(value = sum(value, na.rm=TRUE))
# filter out location data/names that do not match
df.other <- filter(df,
!(subs %in% suburb.names),
!(subs %in% c('STATEWIDE',
'METRO VARIOUS',
'NON-METRO VARIOUS',
'RURAL VARIOUS',
'NORTH-WEST METRO',
'BARWON',
'CASEY',
'LATROBE', 'GIPPSLAND', 'LATROBE VALLEY',
'MONASH',
'BANYULE',
'YARRA RANGES',
'STONNINGTON',
'GLEN EIRA',
'MORNINGTON PENINSULA',
'PHILLIP ISLAND',
'PORT PHILLIP'))) %>%
arrange(desc(value)) %>%
group_by(subs) %>%
summarise(value = sum(value, na.rm=TRUE))
ggplot(df.other, aes(x=subs, y=value)) +
geom_bar(stat='identity') +
coord_flip() +
ggtitle('Location Expenditure not matched to a Suburb') +
xlab('Locations') + ylab('Expenditure A$') +
scale_y_continuous(labels=dollar)
As the expenditure dataset contained spending at multiple spatial scales the higher order groupings needed to be isolated and the expenditure spread across the nested suburbs. As there was no clear pattern to this and the spatial hierarchy was not clearly defined this process is essentially manual.
Higher order spatial groupings included regional, metropolitan and local government areas as well as other spatial groupings.
# Calculate per suburb spending for each identified higher order spatial group
statewide.value <- filter(df, subs == 'STATEWIDE')$value / length(suburb.names)
metro.value <- sum(filter(df, subs == 'METRO VARIOUS')$value) / nrow(filter(ssc, GCCSA_NAME_2011 == 'Greater Melbourne'))
regional.rural.value <- sum(filter(df, subs %in% c('NON-METRO VARIOUS', 'RURAL VARIOUS'))$value) / nrow(filter(ssc, GCCSA_NAME_2011 == "Rest of Vic."))
nw.metro.value <- sum(filter(df, subs == 'NORTH-WEST METRO')$value) / nrow(filter(ssc, SA4_NAME_2011 == "Melbourne - North West"))
barwon.value <- sum(filter(df, subs == 'BARWON')$value) / nrow(filter(ssc, SA3_NAME_2011 == "Barwon - West"))
casey.value <- sum(filter(df, subs == 'CASEY')$value) / nrow(filter(ssc, SA3_NAME_2011 == "Casey - North"))
latrobe.gipps.value <- sum(filter(df, subs %in% c('LATROBE', 'GIPPSLAND', 'LATROBE VALLEY'))$value) / nrow(filter(ssc, SA4_NAME_2011 == "Latrobe - Gippsland"))
monash.value <- sum(filter(df, subs == 'MONASH')$value) / sum(grepl('Monash', ssc$SA3_NAME_2011, TRUE))
stonnington.value <- sum(filter(df, subs == 'STONNINGTON')$value) / sum(grepl('STONNINGTON', ssc$SA3_NAME_2011, TRUE))
banyule.value <- sum(filter(df, subs == 'BANYULE')$value) / sum(grepl('BANYULE', ssc$SA3_NAME_2011, TRUE))
yarra.value <- sum(filter(df, subs == 'YARRA RANGES')$value) / sum(grepl('Yarra', ssc$SA3_NAME_2011, TRUE))
gleneira.value <- sum(filter(df, subs == 'GLEN EIRA')$value) / sum(grepl('GLEN EIRA', ssc$SA3_NAME_2011, TRUE))
mornington.value <- sum(filter(df, subs == 'MORNINGTON PENINSULA')$value) / sum(grepl('mornington', ssc$SA3_NAME_2011, TRUE))
phillip.island.value <- sum(filter(df, subs == 'PHILLIP ISLAND')$value) / sum(grepl('Phillip Island', ssc$SA2_NAME_2011, TRUE))
pt.phillip.value <- sum(filter(df, subs == 'PORT PHILLIP')$value) / sum(grepl('PORT PHILLIP', ssc$SA3_NAME_2011, TRUE))
# add values to SSC
ssc.vals <- left_join(ssc, select(df.suburbs, SSC_NAME_2011 = subs, value)) %>%
mutate(value = ifelse(is.na(value), 0, value),
value = value + statewide.value,
value = ifelse(GCCSA_NAME_2011 == 'Greater Melbourne', value + metro.value, value),
value = ifelse(GCCSA_NAME_2011 == "Rest of Vic.", value + regional.rural.value, value),
value = ifelse(SA4_NAME_2011 == "Melbourne - North West", value + nw.metro.value, value),
value = ifelse(SA3_NAME_2011 == "Barwon - West", value + barwon.value, value),
value = ifelse(SA3_NAME_2011 == "Casey - North", value + casey.value, value),
value = ifelse(SA4_NAME_2011 == "Latrobe - Gippsland", value + latrobe.gipps.value, value),
value = ifelse(grepl('Monash', ssc$SA3_NAME_2011, TRUE), value + monash.value, value),
value = ifelse(grepl('STONNINGTON', ssc$SA3_NAME_2011, TRUE), value + stonnington.value, value),
value = ifelse(grepl('BANYULE', ssc$SA3_NAME_2011, TRUE), value + banyule.value, value),
value = ifelse(grepl('YARRA', ssc$SA3_NAME_2011, TRUE), value + yarra.value, value),
value = ifelse(grepl('GLEN EIRA', ssc$SA3_NAME_2011, TRUE), value + gleneira.value, value),
value = ifelse(grepl('mornington', ssc$SA3_NAME_2011, TRUE), value + mornington.value, value),
value = ifelse(grepl('Phillip Island', ssc$SA2_NAME_2011, TRUE), value + phillip.island.value, value),
value = ifelse(grepl('Port Phillip', ssc$SA3_NAME_2011, TRUE), value + pt.phillip.value, value))Import Population Data
The population data is needed to calculate the expenditure per person for each Suburb. Again there is a problem with matching the location data. The State based Suburb data is not compatible with the ABS based population data. The ABS data contains roughly half the number of suburbs and so cannot be directly linked. To avoid this problem the ABS defined suburbs (SSC) were used
Merge Suburb and Population Data
Merge is made on the ABS suburb ID.
ssc.pop <- left_join(ssc.vals, pop) %>%
mutate(expenditure = value / population)
ggplot(ssc.pop, aes(x=score, y=expenditure)) +
geom_point() +
ggtitle('Expenditure per Person for each Suburb by Socio-Economic Score') +
scale_y_continuous(labels=dollar)
gm = filter(ssc.pop, GCCSA_NAME_2011 == 'Greater Melbourne')
reg = filter(ssc.pop, GCCSA_NAME_2011 == 'Rest of Vic.')Regional Data
The plot of expenditure vs socio-economic advantage/disadvantage is made using the javascript plotting library C3. The values used for the plots are created here with the first 100 shown.
x axis
1043.04,1032.04,921.1,958.73,1034.35,1007.97,1005.54,972.55,934.11,1041.72,1031,963.05,957.65,910.21,1024.37,964.06,1047.98,1044.33,1055.53,980.32,1022.22,981.58,895.97,1070.52,979.8,972.74,1035.88,947.86,908.17,955.74,965.96,996.94,928.4,983.57,1003.91,1030.93,1008.45,981.68,1023.9,1002.5,1011.57,1026.85,1067.99,976.17,1022.3,1026.76,773.35,1010.83,1062.66,991,1036.64,1101.62,1032.79,1072.42,955.19,1096.8,NA,NA,1075.26,900.67,851.37,890.11,866.92,975.42,987.66,916.13,940.18,1074.14,1020.46,1134.27,977.69,892.07,1054.39,922.99,959.98,967.81,925.38,1017.85,912.41,979.21,1016.92,899.97,996.99,1015.89,995.41,1015.58,NA,962.1,1004.72,953.03,997.62,1009.25,959.9,963.26,1022.78,1001.09,1004.65,1062.99,958.04,1073.44,1025.91,949.79,1039.71,NA,1037.07,1013.49,970.4,1038.39,970.83,960.06,993.21,869.04,1102.96,982.97,1055.34,881.56,979.96,975.97,1058,945.09,1002.75,1002.18,989.67,1059.65,943.4,1012.76,947.79,1010.21,1048.05,984.84,1081.79,988.65,1040.68,1061.19,1059.6,1006.93,1036.05,1024.23,1052.79,1022.81,1016.46,898.72,1074.06,968.82,1083.81,1019.41,1010.71,1046.79,854.78,973.92,1011.22,996.36,1036.86,987.59,927.02,999.61,812.76,1023.48,1003.02,999.88,995.93,959.6,1008.41,1073.16,1013.84,979.48,949.63,1071.52,979.85,960.92,916.84,1025.27,950.18,1042.09,1002.04,1114.53,953.26,960.52,949.79,964.91,984.81,965.27,910.01,1027.86,1002.66,991.41,1063.16,892.08,1037.33,928.7,887.96,1031.14,1059.04,944.22,896.25,715.63,998.46,1010.64,918.55,942.46,1013.83,987.32,1036.41,1067.24,1002.9,1046.84,1003.01,924.76,844.21,850.28,996.75,958.9,939.97,948.45,1013.99,964.31,940.42,1026.66,1015.16,996.62,996.73,1114.02,954.71,1020.24,965.68,928.71,1031.69,911.25,949.54,1045.97,1038.07,1015.69,1018.72,932.3,1017.98,973.56,944.12,966.16,985.36,1016.24,996.62,988.85,1012.06,899.38,981.13,1071.76,939.56,1026,1076.97,998.66,955.35,995.48,1015.93,1028.65,990.04,1091.13,1006.22,1019.43,1004.6,936.46,1016.82,986.56,839.72,1016.58,977.72,894.84,820.98,972.65,1016.12,1054.81,1036.42,1026.8,NA,946.32,1011.37,1015.95,1046.34,928.08,998.09,884.37,965.5,937.94,1001.26,1000.78,1028.27,948.95,900.82,1005.91,1070.73,978.26,1012.08,998.49,940.78,1000.27,916,928.2,981.03,1025.13,1076.92,NA,979.62,961.47,1015.69,967.45,977.92,1044.07,936.72,961.47,677.24,1008.74,1007.01,1083.38,977.62,1012.69,1020.93,977.33,993.36,1015.64,986.88,1031.52,1006.45,952.06,997.55,NA,1011.28,1061.45,1065.54,931.64,1017.94,962.71,1019.61,1028.82,1034.12,1029.64,1093.43,1021.34,983.35,958.19,989.56,1012.94,966.2,1103.86,826.26,963.1,957.58,943.69,1028.59,1002.79,972.56,1038.8,992.56,1012.95,986.62,972.33,1040.65,1038.47,874.85,1037.7,1047.9,997.74,1056.15,1037.05,1027.94,995.05,946.18,942.67,1034.1,997.37,1043.75,995.43,1003.48,960.19,985.66,971.08,1042.99,1077.93,1006.61,994.22,981.26,NA,1053.99,1016.21,926.67,1015.53,1061.73,1038.88,895.82,997.78,972.63,928.41,947.59,1039.94,1059.9,893.2,903.89,1025.84,1067.08,1009.49,984.73,947.17,949.37,1011.11,NA,1017.1,979.42,982.53,1040.64,1081.06,1078.46,956.98,1046.33,864.35,1012.04,1028.06,1011.41,1103.67,1050.95,942.71,1013.79,974.53,1000.68,988.13,931.44,1088.13,1023.39,1046.78,878.23,1033.29,988.54,938.65,951.12,1071.74,1010.87,946.23,921.14,962.02,1043.23,1038.77,937.31,998.35,988.24,1016.2,983.11,913.37,1021.48,1047.77,1023.8,1042.3,1064.26,1018.94,990.58,1060.53,994.08,1009.07,1057.16,983.32,969.63,1035.02,1029.11,914.79,965.12,1047.79,983,1036.86,985.94,808.29,942.66,977.27,968.29,993.23,1016.28,930.53,973.32,1015.36,1054.86,1022.88,1024.98,1037.06,935.03,1020.26,991.9,992.38,1057.25,970.75,NA,1059.25,894.53,979.65,1007.42,977.4,1046.45,895.75,1017,1013.09,994.07,1034.67,1051.28,919.17,1029.55,970.65,1028.56,1069.83,966.23,1045.17,1047.38,1009.53,998.24,933.45,NA,953.31,991.45,933.25,932.36,NA,1015.67,832.45,930.79,1002.57,1047.5,822.18,1030.47,1044.64,940.21,982.84,1027.69,1004.2,955,1014.63,1004.89,981.4,990.93,945.72,1037.84,1052.85,1020.96,921.03,965.03,935.48,967.31,974.47,1081.31,1091.48,1050.28,1018.06,1075.72,1009.98,NA,983.76,1061.28,1008.72,1023.54,999.9,1025.05,1005.04,1005.54,868.3,976.92,1068.7,1077.75,1000.79,842.58,1021.83,1002.38,984.8,1021.14,1042.29,871.91,954.39,986.15,1034.92,941.22,925.62,1055.22,1010.04,1034.47,1004.14,984.17,982.95,1057.23,907.08,999.42,1040.91,1052.47,919.01,1031.02,984.62,974.07,980.65,1077.48,838.11,1043.8,984.06,942.66,1040.9,895.44,1016.91,1070.71,1012,933.29,845.23,928.45,909.26,1025.98,962.55,1000.15,1084.4,991.94,1004.26,1086.47,1048.22,923.11,1032.49,1023.45,1095.62,959.74,1009.27,1014.38,1040.5,1054.82,947.43,844.84,1046.72,934.24,NA,1025.06,996.47,938.34,1018.31,971.65,1027.54,911.56,930.34,916.49,1042.66,1032.63,931.11,1010.91,1047.66,984.32,1101.64,1028.28,1015.97,1028.9,1021.4,936.76,983.7,1021.28,1059.93,1001.03,1046.2,1001.93,962.72,994.93,1040.95,1077.88,1020.24,915.34,986.69,892.09,1088.61,983.24,1023.95,976.81,1021.62,958.93,966.59,949.44,1059.02,940.84,1018.22,969.73,962.18,1055.55,1019.8,912.81,1022.07,750.09,1042.08,916.2,938.2,932.06,1033.24,939.54,922.44,1006.9,924.39,997.02,894.43,872.69,989.25,NA,1037.04,955.39,875.72,982.24,1079.88,1045.41,942.39,1037.61,949.8,1068.81,1056.08,980.61,953.89,997.16,975.06,1017.41,953.5,1004.01,939.72,1025.66,988.41,1046.07,834.53,988.84,1050.15,1000.85,1016.27,989.8,1019.16,991.73,880.73,1027.07,997.84,905.24,947.25,914.64,1006.95,1024.74,1037.95,1079.76,1019.9,966.58,923.71,957.5,1019.61,1009.02,1049.52,973.22,946.37,1075.37,851.74,959.83,934.86,898.09,885.66,978.64,980.15,985.79,967.88,1009.15,1035.96,1098.08,1038.56,908.07,824.91,969.69,962.79,986.34,933.95,1013.11,957.81,1033.27,930.07,1038.41,1035.37,1033.49,1032.9,966,890.07,1105.46,956.6,951.56,960.63,994.26,910.85,993.9,1009.4,1005.26,951.87,1033.71,899.66,906.68,991.51,959.31,880.08,1068.37,990.76,1022.35,888.16,1017.73,1024.54,1046.85,1046.81,907.96,991.12,1060.74,1043.43,1014.4,1026.71,969.92,1008.57,983.98,1035.67,967.85,902.48,994.08,910.83,1017.05,1027.17,1010.18,998.52,966.22,959.66,902.57,989.19,921.21,1086.92,931.09,1004.16,899.36,1013.5,1027.29,997.79,1003.3,986.4,1017.92,1058.05,1072.45,936.55,1044.6,1025.11,1024,997.31,930.51,1003.49,942.63,996.68,929.46,1003.77,949.69,992.12,919.15,1080.61,929.72,1067.07,1014.54,1024.22,1003.54,996.73,1042.87,942.29,NA,1023.36,972.64,909.25,1045.89,1053.78,1013.84,1036.55,949.98,1026.72,1085.44,1007.43,1029.47,985.63,1019.48,1027.14,957.11,934.63,962.88,856.27,949.24,979.39,975.33,1015.24,1021.76,1015.95,NA,915.9,980.32,1020.36,999.93,1038.12,979.5,920.08,1001.41,625.6,1077.52,953.8,977.72,1017.09,1053,1018.34,967.15,1077.79,1069.87,1006.96,1009.72,977.97,844.22,1046.46,1028,961.72,1016.7,1052.27,992.73,958.35,979.23,993.25,1031.85,1025.59,909.38,1042.56,969.16,972.02,934.38,1029.81,1117.79,1055.89,1078.24,1030.04,1004.91,1030.5,972.64,1109.01,997.93,1042.84,1067.91,933.51,1030.23,971.7,1062.92,924.59,981.98,1067.65,1038.3,1008.59,971.7,965.29,1059.59,1057.83,1000.35,1055.85,944.19,1081.23,854.48,1017.09,963.95,922.52,991.63,855.44,990.81,917.68,968.23,1026.58,967.08,957.24,1021.95,1014.29,817.08,986.5,981.86,937.11,1004.63,1050.28,978.93,NA,959.73,940.78,1038.07,1015.25,1027.57,1026.15,931.18,1013.91,878.61,902.82,1051.14,1067.17,1039.77,940.54,970.62,987.89,1011.85,885.08,1089.26,1028.49,1012.44,899.39,1074.64,967.96,957.77,1014.82,924.7,993.34,1003.24,1002.75,1037.25,1000.37,NA,924.81,1006.51,1005.04,980.99,995.46,1029.72,984.45,1050.15,901.51,944.96,964.53,957.74,991.5,1012.52,979.04,1034.97,1053.45
y axis
32931.45,12395.55,35897.86,4694.69,1226.12,6268.2,42026.8,33814.67,37238.94,14611.97,3597.89,8082.08,27155.78,1094.18,39051.46,16343.76,25001.78,6552.06,33685.61,68870.71,33307.99,8397.36,7026.77,29418.76,15565.48,19020.75,29816.31,32687.51,1491.58,51015.2,8488.19,20636.08,2789.39,2248.57,34475.11,36620.87,26583.22,31749.64,16842.8,20766.19,15164.31,19720.74,4659.78,25881.61,46450.68,21525.92,48760.38,9299.93,26663.53,19269.93,30967.12,345605.95,24447.73,3455.52,22566.86,93660.15,NA,NA,43691.23,29517.15,65862.9,9882.51,39400.13,18386.73,3001.86,1669.62,1760.9,13828.61,22174.95,54144.96,806.89,32508.9,38901.86,1666.92,37042.01,27845.18,26794.08,25001.78,21063.55,27494.17,50145.62,17616.03,40249.11,16435.06,13747.09,23661.2,NA,25072.81,39564.02,13392.46,14883.02,11926.53,4145.43,29616.2,51015.2,19329.62,10684.78,30121.6,16972.36,47195.88,52848.08,9260.5,55015.06,NA,25881.61,42635.89,19492.42,40859.39,35022.34,27666.55,22746.47,8373.46,19877.54,8632.82,54810.22,24179.81,7556,33685.61,39576.81,8523.86,43691.23,21421.43,2869.19,25957.73,11621.93,35587.21,24086.9,26794.08,18782.92,18122.44,46450.68,41049.44,16226.08,51367.65,19967.49,31304.87,14636.2,21791.68,2729.86,33944.73,25655.9,38539.86,15009.57,34475.11,27494.17,37878.24,30538.51,17170.48,2077.11,25144.24,25403.13,27671.27,22746.47,6268.2,2950.77,2754.57,55342.53,34746.57,13058.35,6575.76,25072.81,22174.95,48227.48,13619.8,17476.49,11358.6,7707.97,19269.93,21525.92,37641.88,6892.32,51015.2,4329.44,35302.52,23535.01,37238.94,25047.33,56939.54,34207.86,6711.5,51367.65,5391.34,2508.21,41827.63,44485.86,1389.37,24993.4,5329.49,18935.94,4865.29,3111.7,39225.02,25001.78,10086.02,1014.67,28747.98,66860.83,30019.15,7117.44,34207.86,25730.7,21113.95,14031.21,24721.65,31517.1,18979.85,39755.09,14781.47,985.48,13132.13,32931.45,23043.42,6086.64,6150.17,24727.51,20382.52,3003.96,25216.08,25288.34,21473.55,26034.3,39225.02,20288.8,21525.92,15367.84,62399.63,13856.25,33304.26,7345.15,24860.93,186310.48,28654.64,27494.17,2192.31,22349.87,5675.65,118155.88,17237.56,24515.64,19369.89,21317.94,39400.13,43051.85,5316.64,27323.93,61717.69,5216.09,20454.01,50145.62,32093.2,3079.17,34953.18,23657.88,25826.51,28469.77,14373.99,20913.81,37878.24,3229.87,22621.76,11720.62,33944.73,9741.31,22119.37,12666.96,2164.48,7162.98,4114.51,7599.28,77479.54,27323.93,4799.97,NA,12420.78,18234.77,22923.71,33304.26,9051.93,29517.15,12040.42,27072.48,38372.3,35444.29,3337.98,23185.9,21113.95,13271.62,13017.15,27753.55,22457.07,14373.99,5947.62,28696.13,25072.81,13964.6,8262.23,23410.16,25805.93,34720.08,NA,60038.29,23657.88,11709.75,2278.76,20572.56,22566.86,36927.32,7155.68,232253.4,25434.09,80848.22,151249.87,27408.79,8077.8,46944.84,39934.97,54479.19,29517.15,31407.93,42000.35,1322.39,23103.74,54691.44,NA,34883.91,205717.82,277595.14,14933.38,21572.03,63040.21,24447.73,44800.15,6801.42,65940.04,42635.89,13790.05,32171.44,32566.9,35587.21,9174.25,33557.52,202108.74,28089.26,33557.52,4035.5,1065.13,52233.4,25361,14539.75,20524.72,7222.28,20893.36,14257.88,23103.74,12978.87,42261.57,12649.72,52533.51,19700.06,15510.77,16496.5,35444.29,35022.34,1470.94,36170.61,16875.01,19877.54,6409.32,29329.8,14420.96,20893.36,1129.53,1453.26,56597.74,29223.94,113384.7,21738,27841.1,26583.22,NA,7342.45,20335.55,35022.34,36203.4,6307.7,28964.32,5708.39,36773.45,19186.15,36620.87,2654.32,21980.01,72939.08,4775.32,7513.5,40299.68,819.3,38901.86,19525.73,15902.03,1318.32,55858.41,NA,17203.96,29031.67,10225.61,19612.51,45728.65,25144.24,9541.22,29223.94,8318.22,13577.89,58280.94,3549.03,10963.51,4684.52,8111.79,16620.77,1756.69,31095.47,29223.94,23043.42,2507.28,30433.2,17443.8,13942.54,17743.41,43476,26564.42,36022.98,3705.13,88256.29,7379,930.58,8240.55,45966.82,30786.57,20288.8,11830.6,7714.71,81718.79,1516.69,3010.26,37555.87,1790.43,34475.11,20288.8,28287.27,28784.97,4741.82,25144.24,29715.92,32556.83,25581.53,43908.6,50722.01,24931.16,46207.48,8096.91,34340.97,24212.36,19103.09,25001.78,4495.99,35587.21,4798.48,38372.3,66860.83,26825.62,39230.15,1204.86,4637.44,26989.69,41827.63,17281.68,26663.53,42430.91,12257.82,18386.73,20615.4,28287.27,5287.97,10711.46,NA,3064.45,2034.03,14733.94,39051.46,25288.34,55673.92,34610.31,41434.88,637.6,45259.64,23535.01,30019.15,29418.76,50432.17,14304.1,29418.76,8543.69,2955.35,21130.78,17059.72,871.93,72912.65,32931.45,NA,23657.88,16873.95,15112.38,15731.96,NA,16612.08,74576.6,11344,11689.57,9730.57,2646.37,6964.45,17901.88,6479.13,21215.45,6326.62,3980.89,8730.09,19061.83,67371.21,34883.91,14304.1,2442.03,13811.63,2932.97,18698.37,18234.77,6519.29,8983.14,14373.99,18541.24,16715.21,28196.9,42430.91,3390.56,46450.68,43476,NA,5350.24,48760.38,40299.68,33179.06,13652.78,26583.22,15960.01,4905.85,1157.76,39934.97,228122.74,26345.16,35161.87,39934.97,43691.23,46450.68,14414.8,47679.72,47965.38,3741.52,15112.38,93914.66,68415.73,23982.69,12997.98,12483.21,29031.67,26744.33,7639.72,33430.41,29223.94,15009.57,4421.4,24515.64,3470.56,35022.34,13192.27,16514.29,4061.84,11689.57,40859.39,121265.24,1457.8,16485.01,8008.74,21473.55,47965.38,2405.62,34883.91,111815.8,53166.44,6508.58,4839.92,30285.16,10723.73,43188.55,30858.84,3030.78,14121.01,15959.55,27323.93,3253.09,273206.28,4157.15,27929.21,28346.17,27929.21,23873.56,24721.65,22514.36,34340.97,52233.4,8437.5,41630.33,216003.72,26907.41,NA,15902.03,32687.51,8896.8,27666.55,22009.05,39755.09,3719.61,47965.38,11691.77,32970.02,38708.9,23225.34,21899.82,16558.4,18698.37,40299.68,34207.86,29917.39,33205.52,31606.9,10144.69,13372.17,36620.87,34475.11,22349.87,35897.86,7786.89,33430.41,28378.23,17443.8,10370.89,33179.06,1338.35,24791.09,1955.17,16842.8,26388.43,4682.03,18595.09,31296.56,20987.69,21723.24,11059.69,910.23,3870.89,5995.67,9756.08,41322.42,33087.35,46944.84,33686.76,53488.66,6493.59,25805.93,2229.82,3691.19,24931.16,24791.09,3666.22,27265.53,29816.31,7506.95,19740.01,18348.5,12865.35,11967.18,NA,702.34,19091.47,3707.16,16683.61,22230.8,24024.66,8126.73,14832.99,32210.32,251349.78,28107.1,20524.72,2871.39,25581.53,12073.36,58063.35,62593.11,26583.22,26424.04,25655.9,55160.18,36319.46,28089.26,47195.88,3580.38,46450.68,24246.23,26042.76,32508.9,9290.14,37949.16,14166.34,3623.79,51941.59,7388.75,1753.19,13251.7,14008.93,25216.08,7647.86,19396.99,14218.76,9938.77,24515.64,28469.77,4567.99,6219.61,21738,11926.53,253544.46,28432.86,17249.62,26825.62,1913.62,2966.6,32210.32,36469.54,53434.17,27841.1,15073.51,22403.72,20103.94,21421.43,25754.97,4560.37,30538.51,18898.56,22983.41,6019.36,25898.45,159633.23,28287.27,5810.97,52222.66,9183.8,31185.97,26111.33,16075.83,7310.65,476697.86,3538.74,11643.31,3668.38,8608.84,45028.72,47195.88,47436.46,16197.81,11432.16,30433.2,14373.99,29329.8,43044.19,51328.07,2002.51,18234.77,28841.92,32809.03,4083.43,61168.06,296657.46,22400.07,219432.35,4461.79,7608.3,16907.34,39075.17,15873.43,20668.92,9541.22,31633.08,44755.71,6163.15,10456.91,15060.8,3127.44,23243.86,86336.94,33179.06,2962.61,23043.42,27753.55,1789.1,3372.42,24313.03,4412.81,19061.83,9634.97,27323.93,1433.2,20893.36,27345.72,3558.19,33685.61,28654.64,1551.08,38206.19,1897.58,9388.97,29616.2,37949.16,28107.1,29816.31,29969.16,14618.05,2570,19922.41,14899.91,17271.29,22137.01,26583.22,12257.82,32566.9,9270.62,37238.94,32809.03,11200.04,15322.27,32508.9,17757.8,20195.95,NA,13397.04,19061.83,29223.94,18310.43,19569.02,25683.83,25361,6062.99,31296.56,26907.41,14832.99,10569.62,35587.21,46671.97,304332.03,25196.6,3742.85,23778.89,5742.11,24447.73,21856.53,11113.81,19289.51,44128.15,16105.16,NA,4727.17,25762.7,11689.57,25144.24,37238.94,10161.25,10529.5,35351.88,73209.02,869.01,31076.16,2564.84,25826.51,48173.81,26188.81,523.56,4969.29,19866.55,22514.36,6246.02,14611.97,30858.84,11338.47,13854.99,38041.5,24246.23,33179.06,26424.04,16164.16,44128.15,23724.81,30751.32,12196.33,15839.09,32447.17,10143.57,23597.94,9916.44,31076.16,17170.48,44485.86,6841.57,34883.91,91152.41,21266.58,29223.94,4519.01,29917.39,29517.15,23982.69,1437.82,15295.72,29418.76,32093.2,3216.34,4090.1,23839.86,43051.85,14733.94,28561.91,1358.54,35731.29,27494.17,19396.99,24339.12,19396.99,4075.25,8987.4,63953.83,20477.1,21275.85,31185.97,833,27666.55,24931.16,628.16,20477.1,19354.45,2696.5,23661.2,20864.37,3324.27,17103.93,32687.51,15060.8,16368.92,17722.15,32060.5,NA,26252.06,35229.96,248637.37,29418.76,29517.15,47449.62,843.04,48934.45,4014.35,60866.41,1894.24,42227.89,21684.59,19013.39,19396.99,49193.36,17137.14,45728.65,26345.16,44485.86,23853.05,28378.23,28561.91,16343.76,8600.87,5449.91,11228.54,37716.36,24652.6,34610.31,4765.46,29223.94,NA,10539.34,27155.78,24339.12,28561.91,28561.91,16134.61,6108.77,35622.78,4288.54,2330.49,16620.77,7712.63,31746.87,32809.03,11422.05,13939.35,24791.09
Greater Melbourne
x axis
1070.92,1086.14,992.49,880.86,1090.39,892.05,1089.54,1004.38,966.97,913.1,881.24,1102.9,1115.76,1102.63,1010.46,1078.64,1033.9,1067.31,1071.86,1056.98,992.24,1045.05,961.89,954.06,1052.51,1056.34,1064.73,1095.51,1104.24,824.31,961.98,1003,987.45,993.66,1065.38,1111.26,1128.67,1039.64,1065.68,1076.22,907.27,1061.61,1071.01,1057.12,1061.13,1040,1025.54,1115.44,1086.53,1050.76,1053.17,1013.54,987.64,1016.21,1039.79,993.65,1118.41,1021.07,1040.97,1057.9,NA,838.61,1039.68,1129.9,1108.74,796.78,994.65,945.41,1047.08,1059.88,1025.76,1020.21,1051.97,1098.68,1026.48,1000.37,1087.54,955.44,1032.37,1044.57,1042.64,1000.39,NA,994.13,1130.27,827.46,970.74,1140.43,1056.79,1025.2,979.79,1094.57,1054.2,1047.8,985.73,957.88,1043.19,990.17,1082.87,1058.28,1103.88,1104.38,1024.41,992.12,1012.73,1040.17,1040.24,1087.83,1014.76,973.19,1029.74,993.84,951.63,1051.72,1093.83,1050.4,1034.21,1013.08,971.45,1004.05,NA,1041.48,1018.84,937.27,816.34,1016.88,1098.98,978.45,928.36,1015.24,984.88,1046.9,932.44,1127.9,951.69,1004.84,1088.03,1065.67,1023.91,795.84,863.34,936.81,842.32,1001.2,1053.9,1118.29,919.02,926.15,1035.51,1018.69,1089.5,1085.93,964.07,1058.66,1066.28,1113.3,1015.69,1057.27,1065.11,1097.22,1093.16,842.72,965.59,1129.24,1126.08,934.24,1080.67,1035.74,1086.2,1101.61,1115.11,1095.29,1041.78,1003.5,973.74,1072.97,NA,1033.91,1060.73,850.88,1111.38,1051.97,904.43,1009.16,1092.39,1061.33,986.83,1068.68,891.61,1072.81,956.07,1025,945.44,807.64,1055.96,1067.67,992.87,1057.52,1018.45,1072.93,1120.68,971,989.72,1051.95,1122.49,1069.77,928.81,1046.68,1071.2,1062.86,1062.94,1069.77,1098.49,931.06,948.18,1106.2,1009.27,932.65,1103.82,896.88,1099.67,1112.54,976.21,1027.63,1029.17,1092.42,1057.47,1067.83,980.79,829.71,1083.83,1053.04,1038.11,1078.08,1047.04,1081.69,979.9,1037.09,1038.65,1020.29,1064.01,994.23,1080,1128.23,884.07,910.08,1076.67,1074.3,1138.67,983.83,1073.88,977.12,1019.3,1044.39,1079.36,986.34,1059.51,1119.1,1104.5,978.52,988.67,1058.71,991.06,1038.93,1033.93,852.69,959.87,1026.39,1030.94,948.95,1143.42,958.63,885.05,1006.46,934.44,1025.83,1067.15,999.67,878.55,684.98,1022.31,1025.88,1070.73,1099.47,1024,1095.53,1109.89,1130.19,1072.95,1084.08,1062.43,985.1,958.17,1090.54,1122.14,1105.96,1063.21,1052.23,1019.5,1094.54,837.43,1053.42,1046.7,884.41,881.22,970.55,1053.72,1066.68,1055.66,1123.32,1071.39,1091.56,1115.99,1006.31,867.82,1055.72,1013.07,1085.06,1118.54,1091.06,1062,1042.77,1064.7,1040.12,NA,1087.72,1026.7,1051.34,1004.5,995.56,1067.18,1116.12,1028.52,1118.88,1077.49,1073.27,1040.82,1069.77,1002.03,1063.34,985.4,1096.42,1096.98,1032.34,1078.37,1068.21,1045.07,887.65,937.28,989.89,1134.59,1056.3,1040.41,1035.17,1097.68,1035.48,1043.62,1038.84,1028.13,1019,1067.12,1074.02,1066.42,981.44,1014.76,1099.35,1080.92,1151.2,1066.47,1077.67,1004.49,1039.24,1065.67,1026.49,1001.51,1124.51,945.1,1105.93,1099.28,1084.26,1136.94,865.83,1075.4,982.59,1076.68,NA,1074.43,1091.93,1113.5,939.42,1033.75,1081.26,1002.47,1025.46,1092.7,1055.91,902.34,1024.8,1072.1,924.06,883.59,1076.38,933.72,971.68,999.96,1150.97,1111.85,1095.7,1035.11,1011.23,963.65,1030.85,1043.9,1055.84,1028.48,1017.16,1073.59,1080.77,1032.48,1016.76,1076.98,1071.61,NA,1008.28,1043.86,1018.04,1050.11,1036.76,1170.81,1080.7,1110.05,1034.62,876.96,887.55,857.35,1064.01,1042.08,1069.69,1059.27,1067.52,1097.21,1059.61,1100.72,1086.93,1069.93,1023.49,916.14,870.84,883.28,1127.16,997.33,1023.2,1049.64,1048.77,1027.66,1108.36,1074.73,1040.24,1083.35,884.88,1023.31,982.62,1055.37,1043.68,1033.6,991.87,1123.69,928.52,NA,1077.29,NA,1003.05,1071.13,955.49,1021,991.63,1080.86,1030.56,1008.17,1052.78,1062.33,1089.81,1072.14,1009.01,1034.19,1016.07,1019.1,1057.23,1064.19,925.13,961.36,1124.77,1143.99,1110.24,946.94,1159.13,997.79,1047.55,1081.02,947.7,1005.67,949.82,971.19,1088.32,978.28,1090.85,1000.93,1083.27,1074.17,1082.21,989.22,1056.63,1028.73,1123.87,994.56,957.39,982.66,1068.65,1036.29,1003.87,1042.57,958.42,1097.73,1053.9,1011.7,1003.06
y axis
10251.67,13944.43,7633.75,9631.47,10142.98,11645.08,22917.84,5686.54,2687.09,4420.45,17811.57,5842.04,110910.4,134621.11,3731.53,6635.04,9224.68,7606.24,7811.11,35952.04,4837.05,69071.77,10662.96,26205.22,9623.07,19961.61,142607.11,3896.41,2477.04,843784.25,23491.35,107822.96,4476.25,6254.76,7851.76,17717.57,4424.04,12712.38,36817.57,30341.23,29131.66,181780.94,4471.76,2759.96,3749.53,90414.93,14931.36,8482.7,3935.7,7166.71,4758.39,23328.52,41541.76,8814.7,445580.76,2730.96,29105.97,51532.28,4593.02,6532.61,NA,6150.4,16384.19,2370.01,3686.02,5022.28,8251.94,30647.18,2650.8,5929.5,3829.44,81340.89,4629.34,87794.39,1959.58,22469.13,67605.95,11501.44,10064.63,3824.79,5186.87,5700.59,NA,207215.31,2517.16,9719.23,91219.78,6417.71,139455.57,297033.97,5133.86,8119.69,3107.4,2525.47,14022.09,2636.58,146802.68,192188.25,9812.59,41650.5,3473.09,4489.78,6509.84,6973.18,9713.22,3545.15,5528.09,146375.93,54041.24,6734.43,122668.83,21025.48,4328.12,138333.3,8698.87,132160.95,65735.41,2154.65,8072.28,11464.78,NA,124329.19,21496.56,7793.04,16298.13,216108.67,80953.89,4991.97,2809.05,6137.65,3455.48,29918.79,5761.25,32100.98,17769.42,2349.96,9718.84,6548.75,11108.17,8203.18,2976.68,2618.51,290692.77,6778.85,124977.89,21246.13,3109,5969.57,10165.08,31333.74,372987.56,4367.54,23286.1,20233.04,149455.3,14191.17,89940.06,2740.76,1862.25,4269.04,7651,7341.17,17197.27,12833.66,10665.82,56782.9,41996.1,9245.93,5183.67,2919.79,7716.94,4218.67,8920.23,2506.72,26265.86,3420.05,NA,21978.75,37885.59,33325.27,65055.97,8479.85,4213.85,1969.31,32897.74,136810.07,5338.25,4397.28,6679.05,58361.75,15389.99,5006.79,4917.84,9649.09,2855.95,52557.97,34253.96,229923.84,23365.81,9043.85,66146.46,6372.6,121658.06,10944.92,2340.15,1752.15,2701.3,151326.51,19286.71,2502.08,4620.45,59185,133563.18,9911.48,6055.57,4033.11,11459.56,2329.39,80398.3,5814.66,2377.4,3887.73,8041.94,211568.57,60087.49,18164.98,5815.69,38429.71,7904.29,9448.4,164955.29,4903.91,3080.47,46528.03,82298.1,89120.92,1339.4,7174.11,136458.86,28542.59,14341.59,138333.3,4543.2,13368.11,27350.98,43595.95,32286.18,41694.07,49609.18,15924.52,9247.15,4878.72,4761.64,28724.08,578773.8,20846,5172.93,4411.41,8378.26,2557.24,5017.08,16493.06,37324.2,177926.93,47369.07,6065.93,14946.07,14374.34,7054.26,38972.52,63738.57,5448.31,4001.68,22516.15,55986.13,2357.25,44758.51,20227.48,16023.96,578773.8,3216.86,36199.37,146375.93,13357.48,7444.31,14473.5,8135.61,66578.48,58025.85,28695.63,5245.4,21050.72,6493.85,107869.48,5383.05,2547.89,4950.19,92391.41,19781.71,10714.11,3579.7,53483.23,379397.17,23838.54,5643.73,3548.76,4356.54,55226.35,13904.34,122234.66,190302.65,123486.87,12743.87,1846.38,32348.38,8955.83,15468.53,165986.26,10174.44,10120.4,5914.34,7476.09,3713.92,9520.39,NA,49797.78,2600.56,7442.43,2251.89,73723.75,40520.06,2928.24,5379.88,40239.09,5319.14,1588.37,2883.68,5896.93,49953.69,70920.17,2380.38,102998.85,9910.7,1760.11,22326.86,4196.11,10911.18,1951.05,6813.71,4937.75,17051.58,2326.89,23541.37,4595.96,349675.84,9195.91,180665.32,8933.62,9469.81,5577.83,35996.2,30506.62,6556.13,1572.61,213361.53,46796.77,43370.65,13561.36,4502.67,24575.25,3833.26,5270.39,6634.17,12997.76,200610.84,24331.74,120462.49,3025.55,315518.22,3564,199030.28,250579.28,6731.83,1682.88,24331.61,NA,68777.81,43110.94,19029.98,1266.76,2158.16,16735.66,7713.89,7848.47,5205.02,35134.34,42135.76,11998.1,8541.86,4031.54,11044.17,1473.78,2765.14,6188.15,14378.5,16439.22,6278.48,43607.3,8614.04,10137.57,3441.62,26984.63,10388.55,30396.16,21242.7,60536.58,188638.33,117675.79,41522.21,6729.93,129111.08,37037.21,NA,5068.65,34743.92,27545.58,6193.34,2411.79,799259.06,2699.65,4480.63,21436.07,2636.01,4257.95,7138.06,44130.87,59113.48,17827.3,4314.38,4032.29,18222.84,254375.94,340225.14,7258.5,56686.88,3122.04,7546.68,18831.97,3007.96,3837.91,4871.17,2783.05,4276.29,3124.24,23972.6,3055.98,3965.43,13245.11,48243.71,2476.68,2890.55,168449.62,158843.28,174837.92,57154.73,37051.74,3975.92,18718.17,NA,29480.87,NA,6274.29,148479.16,51872.61,15288.59,74049,121041.64,21876.03,155411.48,7230.32,5354.59,4406.91,7490.75,10057.9,114729.92,17278.37,39933.01,3657.54,3810.36,23146.34,107363.16,9266.5,77705.74,10525.36,249273.86,23311.72,9805.01,13114.17,26591.45,26380.5,31043.97,42719.62,4926.46,13441.89,22638.38,2706.14,12918.73,96749.73,15594.09,3817.83,32591.15,7234.21,98346.33,13136.79,149416.38,17873.11,3187.87,12223.57,168405.75,236400.57,19312.28,22857.46,33324.5,3880.43,97420.57,140517.47
Merge All data with Spatial Data
By this stage we have all of the location data which has been cleaned and the expenditure per suburb calculated. This has been merged to the ABS data which gives us the population and advantage / dissadvantage index for each suburb.
Now this cleaned dataset is merged with the spatial data so it can be represeted in a map.
abs.sub@data <- merge(abs.sub@data, ssc.pop, by = 'SSC_CODE')The Map
Here is the basic map. The data is subsequently saved to GeoJSON format so it can be used outside of R in the final web map.
pal <- colorQuantile("Blues", abs.sub@data$expenditure, n = 8, na.color = 'grey')
leaflet(abs.sub) %>%
addProviderTiles("CartoDB.Positron", group = 'Default') %>%
setView(lng = 144.97, lat = -37.4, zoom = 7) %>%
addPolygons(stroke = FALSE,
fillOpacity = 0.5,
color = ~pal(expenditure)) %>%
addLegend(position='topright',
pal=pal,
labFormat = function(type, cuts, p) {
n = length(cuts)
paste0(dollar(cuts[-n]), " – ", dollar(cuts[-1]))
},
values=~expenditure)# save spatial data to geojson
rgdal::writeOGR(abs.sub, dsn='abs_sub.geojson', "OGRGeoJSON", driver="GeoJSON")Annual Data for other plots
This part of the data clean is more straight forward as thedata is in better shape. The purpose of the code here is to prototype plots and save data in the correct format for more or less direct pasting into final web page.
Revenue
revenue <- read_excel('data/TaxationRevenueGG.xlsx',
sheet = 'Overview',
skip = 3)
colnames(revenue)[1] <- 'Year'
revenue <- revenue[!is.na(revenue$Year), -seq(3, ncol(revenue), by=2)] %>%
mutate_each(funs(as.numeric), -Year) %>%
rowwise() %>%
mutate(Year = strsplit(Year, '-'),
Year = as.numeric(Year[1]))
dygraph(revenue[,-2], periodicity = 'Year') %>%
dyAxis("x", drawGrid = FALSE) %>%
dyOptions(stackedGraph = TRUE, fillGraph = TRUE, gridLineColor = "lightblue") %>%
dyLegend(width = 500, labelsSeparateLines=TRUE, show='always', labelsDiv='legend')Budget
ann.rev <- select(revenue, Year, tax = `Total taxation`) %>%
mutate(tax = tax * 1e6)
budget <- read_csv('data/VicBudget.csv') %>%
group_by(`Financial Year`) %>%
summarise(budget = sum(Actual, na.rm= TRUE)) %>%
rowwise() %>%
mutate(Year = strsplit(`Financial Year`, '-'),
Year = as.numeric(Year[1]))Population
Here we used the budget economic indicators for annual population estimates. Unfortunately population estimates were missing after 2013. To fill missing data a simple linear model was used to interpolate missing data.
ann.pop <- read_excel('data/2015-16-Budget-MacroeconomicIndicators.xlsx',
sheet = 'Population',
skip = 4)[,1:2]
colnames(ann.pop) <- c('Year', 'Population')
ann.pop <- rowwise(ann.pop) %>%
mutate(Year = strsplit(`Year`, '-'),
Year = as.numeric(Year[1]),
Population = as.integer(as.numeric(Population) * 1000))
# population model
pop.lm <- lm(Population ~ Year, ann.pop)
ann.pop$predicted = predict.lm(pop.lm, ann.pop)
ann.pop <- mutate(ann.pop,
Population = ifelse(is.na(Population), as.integer(predicted), Population))
annual <- left_join(budget, ann.rev) %>%
left_join(., ann.pop) %>%
select(Year, Population, tax, budget) %>%
melt(., c('Year','Population')) %>%
mutate(per.person = as.integer(value / Population))
ggplot(annual, aes(x=Year, y=per.person, colour=variable)) +
geom_line() +
scale_y_continuous(labels=dollar)
to.dygraph <- dcast(annual, Year~variable, value.var = 'per.person')
write.csv(to.dygraph, 'perperson.dygraph.csv', row.names = FALSE)Merge Revenue and Population Data
Mergingg revenue and population gives us taxation per-person by type of tax.
# merge populatio to get tax types per person
rev.dygraph <- left_join(revenue, ann.pop) %>%
select(-`Total taxation`, -Population) %>%
mutate_each(funs(per.person = as.integer((. * 1e6) / predicted)), -Year, -predicted)
write.csv(rev.dygraph[,-grep('predicted',names(rev.dygraph))], 'revenue-dygraph.csv', row.names = FALSE)Wrap UP and Spatial data preparation
The outputs of all the above are used as the basis of the final webpage which can be seen here. Final processing of the GeoJSON file is done using GDAL to simplify the geometry:
>ogr2ogr -f GeoJSON abs_sub_simple.geojson abs_sub.geojson -simplify 0.0001This is done twice. The second time using -simplify 0.001 for Internet Explorer which really struggles to load large GeoJSON files. The final step is to add var region_data = the the start of each GeoJSON to simlplify inprting into the final map.